/*
 */

package Dblayer;

import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author bruhn
 */
public class DBLevering implements IFDBLevering {

    private Connection con;
    public DBLevering()
    {
        con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList<Levering> hentBestilling(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }

    public Levering findLevering(int bestillingsNr, boolean retriveAssociation) {
        String wClause = "  bestillingsNr = '" + bestillingsNr + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    public int opretLevering(Levering levering) {
    int rc = -1;
        String query="INSERT INTO semLevering( bestillingsNr, fornavn, efternavn, tlf, adresse)  VALUES('"+
                levering.getBestillingsNr().getBestillingsNr()  + "','"  +
                levering.getFornavn() + "','" +
                levering.getEfternavn() + "','" +
                levering.getTlf() + "','" +
                levering.getAdresse() + "')";

                  System.out.println("insert : " + query);
                  try{ //indsæt ny levering
                        Statement stmt = con.createStatement();
                        stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in semLevering db: "+ex);
	   }
	   return(rc);
	}

	public void slet(int bestillingsNr)
	{
               int rc=-1;

	  	String query="DELETE FROM semLevering WHERE bestillingsNr = '" +
				bestillingsNr + "'";
                System.out.println(query);
	  	try{ //slet fra levering
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semBestilling db: "+ex);
   	        }
    }

    public int redigerLevering(Levering levering) {
       Levering leveringObj  = levering;
		int rc=-1;

		String query="UPDATE semLevering SET "+
                        "bestillingsNr ='" + leveringObj.getBestillingsNr() + "', " +
		    	"fornavn ='" + leveringObj.getFornavn() + "', " +
                        "efternavn ='" + leveringObj.getEfternavn() + "', " +
                        "adresse ='" + leveringObj.getAdresse() + "' " +
                        "tlf ='" + leveringObj.getTlf() + "' " +
		        " WHERE bestillingsNr = '"+ leveringObj.getBestillingsNr() + "'";

                System.out.println("Update query:" + query);
  		try{ //opdater en levering
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in bestilling db: "+ex);
	  	}
		return(rc);
    }

    public void sletLevering(int bestillingsNr) {
        int rc=-1;

        String query="DELETE FROM semLevering WHERE bestillingsNr = '" +
                        bestillingsNr + "'";
        System.out.println(query);
        try{ //slet fra levering
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in semBestilling db: "+ex);
        }
    }
    private ArrayList<Levering> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Levering> list = new ArrayList<Levering>();

	    String query =  buildQuery(wClause);

            try{ //læs semLevering fra databasen
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 Levering leverObj = new Levering();
		 leverObj = buildLevering(results);
                 list.add(leverObj);
		}//slut while
                 stmt.close();
                 if(retrieveAssociation)
                 {   //bestillingen bliver bygget
                     for(Levering leverObj : list){
			IFDBBestilling dbBestillingObj = new DBBestilling();
                        Bestilling bestilObj = dbBestillingObj.findBestillingsNr(leverObj.getBestillingsNr().getBestillingsNr(), false);
                        System.out.println("bestilling is seleceted ");
			leverObj.setBestillingsNr(bestilObj);
                     }
                 }//slut if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

    //Singelwhere bliver brugt når der kun skal findes 1 ting
    private Levering singleWhere(String wClause, boolean retrieveAssociation)
    {
           ResultSet results;
           Levering leverObj = new Levering();

	   String query =  buildQuery(wClause);
           System.out.println(query);
		try{ //læs semLevering fra databasen
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            leverObj = buildLevering(results);
                            //assocaitionen bliver også bygget
                            stmt.close();
                            if(retrieveAssociation)
                            {
                                IFDBBestilling dbBestilObj = new DBBestilling();
                                Bestilling bestilObj = dbBestilObj.findBestillingsNr(leverObj.getBestillingsNr().getBestillingsNr(), false);
                                System.out.println("vare is seleceted ");
                                leverObj.setBestillingsNr(bestilObj);
                            }
			}
                        else{ //ingen levering blev fundet
                            leverObj = null;
                        }
		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return leverObj;
	}
	//metoden til at bygge en "query"
	private String buildQuery(String wClause)
	{
	    String query="SELECT bestillingsNr, fornavn, efternavn, adresse, tlf FROM semLevering";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Levering buildLevering(ResultSet results)
        {
            Levering leverObj = new Levering();
            IFDBBestilling dbBestillingsObj = new DBBestilling();
            try{ //de tables der bliver brugt her er fra semLevering
                leverObj.setBestillingsNr(dbBestillingsObj.findBestillingsNr(results.getInt("bestillingsNr"), false));
                leverObj.setFornavn(results.getString("fornavn"));
                leverObj.setEfternavn(results.getString("efternavn"));
                leverObj.setAdresse(results.getString("adresse"));
                leverObj.setTlf(results.getInt("tlf"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Levering object");
         }
         return leverObj;
      }
}
